﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace LeanApp.Common
{

    /// <summary>
    /// excel 处理工具
    /// </summary>
    public static class ExcelTool
    {
        /// <summary>
        /// 将excel读取到系统中
        /// </summary>
        /// <param name="fileFullPath">文件全路径</param>
        /// <returns>读取结果</returns>
        public static List<Dictionary<string, object>> ReadExcel(string fileFullPath)
        {
            List<Dictionary<string, object>> dtRes = new List<Dictionary<string, object>>();
            if (!File.Exists(fileFullPath))
            {
                return dtRes;
            }
            StringBuilder sbr = new StringBuilder();
            using (FileStream fs = new FileStream(fileFullPath, FileMode.Open, FileAccess.Read, FileShare.Read))
            {
                //读取xls文件中的数据写入wk中
                IWorkbook wk = null;
                if (fileFullPath.EndsWith("xlsx"))
                {
                    wk = new XSSFWorkbook(fs);
                }
                else
                {
                    wk = new HSSFWorkbook(fs);
                }
                if (wk.NumberOfSheets <= 0)
                {
                    return dtRes;
                }
                //读取当前表数据
                ISheet sheet = wk.GetSheetAt(0);
                //首行表头
                List<string> colNames = new List<string>();
                //LastRowNum 最后一行的索引,从0开始
                for (int j = 0; j <= sheet.LastRowNum; j++)
                {
                    //获取当前行数据
                    IRow row = sheet.GetRow(j);
                    if (row == null)
                    {
                        continue;
                    }
                    Dictionary<string, object> rList = new Dictionary<string, object>();
                    bool needAdd = false;
                    for (int k = 0; k <= row.LastCellNum; k++) //LastCellNum 最后一列的索引，从0开始
                    {
                        //获取当前单元格
                        ICell cell = row.GetCell(k);
                        if (j == 0)
                        {
                            string cellTxt = "";
                            if (cell != null)
                            {
                                cellTxt = cell.ToString();
                            }
                            if (string.IsNullOrWhiteSpace(cellTxt))
                            {
                                cellTxt = "第" + (k + 1) + "列";
                            }
                            int existQty = 0;
                            if (colNames.Contains(cellTxt))
                            {
                                existQty = colNames.FindAll(a => a.StartsWith(cellTxt)).Count();
                                cellTxt = cellTxt + (existQty + 1).ToString();
                            }
                            colNames.Add(cellTxt);
                            needAdd = false;
                        }
                        else
                        {
                            if (cell == null)
                            {
                                continue;
                            }
                            string cellTxt = cell.ToString();

                            CellType cellType = cell.CellType;
                            switch (cellType)
                            {
                                case CellType.Numeric:
                                    rList.Add(colNames[k], cell.NumericCellValue);
                                    break;

                                case CellType.Boolean:
                                    rList.Add(colNames[k], cell.BooleanCellValue);
                                    break;

                                case CellType.Unknown:
                                case CellType.String:
                                case CellType.Formula:
                                    rList.Add(colNames[k], cellTxt);
                                    break;

                                case CellType.Error:
                                case CellType.Blank:
                                default:
                                    rList.Add(colNames[k], null);
                                    break;
                            }
                            needAdd = true;
                        }
                    }
                    if (needAdd)
                    {
                        dtRes.Add(rList);
                    }
                }
            }
            return dtRes;
        }
    }
}